﻿using Dapper;
using MySqlConnector;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace duia.Canteenpay.Infrastructure
{
    /// <summary>
    /// Dapper基本帮助类
    /// </summary>
    public class AppDapper
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private static string connectionString;

        public AppDapper(string connString)
        {
            connectionString = connString;
        }

        /// <summary>
        /// 查询列表
        /// </summary>
        /// <param name="sql">查询的sql</param>
        /// <param name="param">替换参数</param>
        /// <returns></returns>
        protected List<T> Query<T>(string sql, object param)
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                return con.Query<T>(sql, param).ToList();
            }
        }

        /// <summary>
        /// 查询第一个数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        protected T QueryFirst<T>(string sql, object param)
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                return con.QueryFirst<T>(sql, param);
            }
        }

        /// <summary>
        /// 分页查询数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">执行sql</param>
        /// <param name="param">参数</param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        protected (int, IEnumerable<T>) QueryPage<T>(string sql, object param, int? pageIndex = null, int? pageSize = null)
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                var countSql = $@"select count(1) from ({sql}) a";
                var count = con.QueryFirst<int>(countSql);

                var data = con.Query<T>($"{sql} LIMIT {pageIndex},{pageSize}", param);

                return (count, data);
            }
        }

        /// <summary>
        /// 分页查询数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">执行sql</param>
        /// <param name="param">参数</param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        protected (int, IEnumerable<T>) QueryPage<T>(string sql, object param, string sortPropertyName, bool ascending, int? pageIndex = null, int? pageSize = null)
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                var countSql = $@"select count(1) from ({sql}) a";
                var count = con.QueryFirst<int>(countSql);

                if (!string.IsNullOrWhiteSpace(sortPropertyName))
                {
                    if (ascending)
                        sql += $" order by {sortPropertyName} asc";
                    else
                        sql += $" order by {sortPropertyName} desc";
                }


                var data = con.Query<T>($"{sql} LIMIT {pageIndex},{pageSize}", param);

                return (count, data);
            }
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">执行sql</param>
        /// <param name="param">参数</param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        protected IEnumerable<T> QueryPage<T>(string sql, object param)
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                return con.Query<T>(sql, param);
            }
        }

        /// <summary>
        /// 查询第一个数据没有返回默认值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        protected T QueryFirstOrDefault<T>(string sql, object param)
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                return con.QueryFirstOrDefault<T>(sql, param);
            }
        }

        /// <summary>
        /// 查询第一个数据没有返回默认值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        protected async Task<T> QueryFirstOrDefaultAsync<T>(string sql, object param)
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                return await con.QueryFirstOrDefaultAsync<T>(sql, param);
            }
        }

        /// <summary>
        /// 查询单条数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        protected T QuerySingle<T>(string sql, object param)
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                return con.QuerySingle<T>(sql, param);
            }
        }



    }
}
